import pandas as pd
from sqlalchemy import create_engine
from datetime import datetime
import openpyxl

engine = create_engine('mysql+pymysql://jsbi:jsbi-1701@47.114.55.19:9011/biv1?charset=utf8')
con = engine.connect()


def funcLambda(x):
    # lambda的多逻辑判断
    if x <= 0.5:
        return "主推款"
    elif 0.5 < x <= 0.8:
        return "次推款"
    elif x > 0.8:
        return "动销款"
    else:
        return ''


# 月份->店铺
# 【月主推次推动销逻辑的添加】
# sqlCmd = " select * from 财务_拼多多月度订单明细 where 交易时间='5月' and 店铺='拼多多-第一森林旗舰店' limit 20000 "
# sqlCmd = " select 店铺,平台商品id,新金额 from 财务_拼多多月度订单明细_debug"
sqlLoop = "select distinct 店铺,交易时间 from 财务_拼多多月度订单明细 where 导入文件名称='拼多多7.6-7.12品类销售占比和毛利分析.xlsx' "
dfLoop = pd.read_sql(sql=sqlLoop, con=engine)
for index, row in dfLoop.iterrows():
    df = pd.read_sql(sql=" select * from 财务_拼多多月度订单明细 where 交易时间='" + row['交易时间'] + "' and 店铺='" + row['店铺'] + "' ",
                     con=engine)

    # 分组（店铺和月份，在页面选择过滤掉了）
    dfGroup = df.groupby(['平台商品id'], as_index=False)
    df1 = dfGroup['新金额'].sum()
    # 纵向的占比
    df1['退款后销售额占比'] = df1['新金额'] / df1['新金额'].sum()
    # 排序
    df1.sort_values(['退款后销售额占比'], ascending=False, inplace=True)
    # 纵向的按照销售占比累计
    # 行和行计算
    df1['退款后销售额占比排序后的累计'] = df1['退款后销售额占比'].cumsum()
    # df1['月主推次推动销'] = df1.apply(lambda x: '主推款' if x['退款后销售额占比排序后的累计'] <= 0.5 else x['月主推次推动销'], axis=1)
    df1['月主推次推动销'] = df1['退款后销售额占比排序后的累计'].apply(funcLambda)

    debug = ''
    for index1, row1 in df1.iterrows():
        engine.execute(
            " update 财务_拼多多月度订单明细 set 月主推次推动销='" + row1['月主推次推动销'] + "' where 店铺='" + row['店铺'] + "' and 交易时间='" + row[
                '交易时间'] + "' and 平台商品id='" + row1['平台商品id'] + "' ")
        print("更新了一个产品的竞争分类")
# filePath = 'D:/简尚家居/excel文件/云杉/6月(1).xlsx'
# wb = openpyxl.load_workbook(filePath)
# #获取workbook中所有的表格
# sheets = wb.get_sheet_names()
#
# for i in range(0, len(sheets)):
#     # 0->4
#     df = pd.read_excel(filePath, sheet_name=i)
#     debug = ''
#     df.head()
#     df.rename(columns={'订单支付金额 ': '订单支付金额'}, inplace=True)
#     df['交易时间'] = '6月'
#     df['插入时间'] = datetime.now()
#     df.to_sql(name='财务_拼多多月度订单明细', con=con, if_exists='append', index=False)
#     print('完成一次数据保存,页:' + str(i))
#     debug = ''
